<?php

class Administration_Model_ApplicantResult extends Zend_Db_Table_Abstract
{
    protected $_name = "admissionApplicantResult";
    
     function listApplicants(){
        return $this->fetchAll()->toArray();
    }
    
    function saveApplicantResult($applicantId,$questionId,$result) {
        
        $row = $this->find($applicantId,$questionId)->current();
        
        if ($row){
            $row->result = $result;
        } else {
            $row = $this->createRow();
            $row->applicantId = $applicantId;
            $row->questionId = $questionId;
            $row->result = $result;
        }
        return $row->save();
    }
    
    function getApplicantTotalQuestionsNumber($applicantId){
        $select = $this->select()->from($this,array('qNo'=>'COUNT(*)'))->group("applicantId")->where('applicantId = ?',$applicantId);
       return $this->fetchRow($select)->toArray();
    }
    
    function getApplicantTotalGrade($applicantId){
        $select = $this->select()->from($this,array('tGrade'=>'SUM(`result`)'))->where('applicantId = ?',$applicantId);
       return $this->fetchRow($select)->toArray();
    }
    
    function getApplicantResultPrecentage($questionNumber,$totalgrade){
        return ((float)$totalgrade/(float)($questionNumber*10))*100;
        
    }
    
    function getApplicantQuestionsCategories($applicantId){
        
        $select = $this->select()->setIntegrityCheck(false)->from($this,NULL)->columns(array('categoryName'=>'admissionCategory.name','questionsNo'=>'count(`admissionApplicantResult`.`questionId`)'))->join('admissionQuestion','admissionQuestion.id = admissionApplicantResult.questionId',NULL)->join('admissionCategory','admissionCategory.id = admissionQuestion.categoryId',NULL)->group('admissionCategory.name')->where('admissionApplicantResult.applicantId = ?',$applicantId);
        return $this->fetchAll($select)->toArray();
    }
    
    function getApplicantQuestionsLevels($applicantId){
        
        $select = $this->select()->setIntegrityCheck(false)->from($this,NULL)->columns(array('levelName'=>'admissionQuestionLevel.name','questionsNo'=>'count(`admissionApplicantResult`.`questionId`)'))->join('admissionQuestion','admissionQuestion.id = admissionApplicantResult.questionId',NULL)->join('admissionQuestionLevel','admissionQuestionLevel.id = admissionQuestion.levelId',NULL)->group('admissionQuestionLevel.name')->where('admissionApplicantResult.applicantId = ?',$applicantId);
        return $this->fetchAll($select)->toArray();
    }
    
    function getApplicantGradesCategories($applicantId){
        
        $select = $this->select()->setIntegrityCheck(false)->from($this,NULL)->columns(array('questionsNo'=>'count(`admissionApplicantResult`.`result`)'))->join('admissionGradesCategories','admissionGradesCategories.value = admissionApplicantResult.result','admissionGradesCategories.grade')->group('admissionApplicantResult.result')->where('admissionApplicantResult.applicantId = ?',$applicantId);
        return $this->fetchAll($select)->toArray();
    }
            
    function getApplicantResultByApplicantId($applicantId) {
        
        return $this->find($applicantId)->toArray();
    }
    
    function editApplicant($applicantId, $applicantInfo) {
        
        return $this->update($applicantInfo, "id = $applicantId");

    }
    
    function deleteApplicant($applicantId) {
        return $this->delete("id = $applicantId");
    }

}

